#!/usr/bin/env python
# coding: utf-8

# In[18]:


import pandas as pd 
import pymysql

# 获取用户信息
try:
    conn = pymysql.connect(host="localhost", user="test", passwd="123456", db='course', charset="utf8")
    cursor = conn.cursor()
    print("数据库连接成功！")
except Exception as e:
    print(e)

sql2 = "select Idx, ListingInfo, target from 6_task_training_master;"
try:
    cursor.execute(sql2)
    res = cursor.fetchall()
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    data2 = pd.DataFrame(res, columns=cols)
    print("数据获取成功！")
    cursor.close()
    conn.commit()
    conn.close()
except Exception as e:
    print(e)
    

import argparse
import numpy as np

# 初始化参数构造器
parser = argparse.ArgumentParser()

# 在参数构造器中添加两个命令行参数
parser.add_argument('--filename', type=str,default="月份影响")

# 获取所有的命令行参数
args = parser.parse_args(args=[])
#args=[]

filename = args.filename # 文件名


# In[31]:


df = data2.copy()
df["ListingInfo"] = pd.to_datetime(df["ListingInfo"]) # 处理日期格式
# df.sort_values(by="ListingInfo", ascending=False, inplace=True, ignore_index=True)
df["月份"] = df["ListingInfo"].map(lambda x:x.month)  # 获取所属月份

# 定义函数
def get_p(df):
    num = len(df[df["target"] == 1])
    p = num/len(df)
    return p

lst = []
for i in range(1,13):
    df2 = df[df["月份"] == i]
    p = get_p(df2)
    lst.append([i,p])

df_yuqi = pd.DataFrame(lst, columns=["用户借款月份","逾期率"])

# 可视化
import warnings;warnings.filterwarnings("ignore")
from matplotlib import pyplot as plt
import seaborn as sns
plt.rcParams["font.sans-serif"] = ["SimHei"]  # 正常显示中文
plt.rcParams["axes.unicode_minus"] = False # 正常显示负号
import seaborn as sns
plt.figure(dpi=100)
sns.barplot(x=df_yuqi["用户借款月份"] , y=df_yuqi["逾期率"])
plt.title("用户借款月份和逾期率的关系")
# plt.show()
plt.tight_layout()
plt.savefig(filename + ".png")

